begin tran

select  case when isnull(b.route, '*') = '*' then b.shop_code else b.route end route, 
a.pickingNo, custrefno, dlvyDate
into #picking
from dcpickingheader a, igsnet.dbo.pos_shop b
where dlvyDate between '2014.07.01' and '2014.07.31' and a.shopCode = b.shop_code and srcType ='P' and a.shopCode in('HKHC','HKNT', 'HKTQ', 'HKVC')
order by case when isnull(b.route, '*') = '*' then b.shop_code else b.route end , custrefno, dlvyDate, pickingNo

declare @route nvarchar(20), @min int 
select @min = 0

create table #tempPickingSeq(pickingNo nvarchar(20), seqNo int identity(1,1 ) )
declare  cu_picking cursor for select distinct route from #picking order by route
open cu_picking
fetch cu_picking into @route
while @@fetch_status = 0
begin
	insert into #tempPickingSeq select pickingNo 
	from #picking where route = @route  order by dlvyDate, custrefNo
	
	select @min = min(seqNo)-1 from #tempPickingSeq
	update dcpickingheader set custRefNo = a.seqNo - @min 
	from #tempPickingSeq a
	where dcpickingheader.pickingNo = a.pickingNo
	delete #tempPickingSeq
	fetch cu_picking into @route
end 
close cu_picking
deallocate cu_picking

drop table #tempPickingSeq
drop table #picking

select  case when isnull(b.route, '*') = '*' then b.shop_code else b.route end route, 
a.pickingNo, custrefno, dlvyDate
into #return
from dcpickingheader a, igsnet.dbo.pos_shop b
where dlvyDate between '2014.07.01' and '2014.07.31' and a.shopCode = b.shop_code and srcType ='R' and  a.shopCode in('HKHC','HKNT', 'HKTQ', 'HKVC')
order by case when isnull(b.route, '*') = '*' then b.shop_code else b.route end , dlvyDate, custrefno, pickingNo

select @min = 0
create table #tempReturnSeq(pickingNo nvarchar(20), seqNo int identity(1,1 ) )
declare  cu_return cursor for select distinct route from #return order by route
open cu_return
fetch cu_return into @route
while @@fetch_status = 0
begin
	insert into #tempReturnSeq select pickingNo 
	from #return where route = @route  order by dlvyDate, custrefNo
	
	select @min = min(seqNo)-1 from #tempReturnSeq
	update dcpickingheader set custRefNo = a.seqNo - @min 
	from #tempReturnSeq a
	where dcpickingheader.pickingNo = a.pickingNo
	delete #tempReturnSeq
	fetch cu_return into @route
end 
close cu_return
deallocate cu_return

drop table #tempReturnSeq
drop table #return

select a.* from dcpickingHeader a, igsnet.dbo.pos_shop b
where a.shopcode = b.shop_code and a.dlvydate between '2014.07.01' and '2014.07.31' and srcType ='P' and 
		b.shop_code = 'HKVC'
order by dlvydate
--select * from dcpickingheader where pickingno in( 'KO-IS0011003', 'KO-IS0011007')
--commit tran
rollback tran